------------------------------------------------------------------------------------
--  PROCEDURE NAME: dbo.UpsertStorage
--     DESCRIPTION: Inserts or updates a record in the Storage table.
------------------------------------------------------------------------------------
--   REFERENCED BY: Upsert-Storage.ps1
--    PROJECT NAME: iSQLPS
------------------------------------------------------------------------------------
--  CHANGE HISTORY:
-- DATE        MODIFIED   DESCRIPTION   
------------------------------------------------------------------------------------
-- 08.10.2010  SYoung     Initial creation
------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[UpsertStorage]
      @ServerSQLID int,
      @DeviceID char(2),
      @VolumeName nvarchar(128),
      @TotalSize decimal(19,2),
      @FreeSize decimal(19,2),
      @FreePercent varchar(12),
      @Active tinyint = 1
  
AS

SET NOCOUNT ON
 
DECLARE @ERRORCODE [int],  
        @ERRMSG [varchar] (128)  
  
IF @freePercent = 'NaN'  
   BEGIN  
   SET @freePercent = 0
END  

IF EXISTS (SELECT 'TRUE'
             FROM dbo.Storage
            WHERE ServerSQLID = @ServerSQLID AND
                  DeviceID = @DeviceID)
   BEGIN
   UPDATE dbo.Storage
      SET VolumeName = @VolumeName,
          TotalSize = @TotalSize,
          FreeSize = @FreeSize,
          FreePercent = @FreePercent,
          UpdateDate = GETDATE(),
          Active = @Active
    WHERE ServerSQLID = @ServerSQLID AND
          DeviceID = @DeviceID
END
ELSE
   BEGIN
   INSERT dbo.Storage (  
          ServerSQLID
        , DeviceID
        , VolumeName
        , TotalSize
        , FreeSize
        , FreePercent
        , CreateDate
        , UpdateDate
        , Active)  
   VALUES (@ServerSQLID
        , @DeviceID
        , @VolumeName
        , @TotalSize
        , @FreeSize
        , @FreePercent
        , GETDATE()
        , GETDATE()
        , 1)
END
  
SET @ERRORCODE = @@ERROR  
IF @ERRORCODE <> 0   
   BEGIN  
   SET @ERRMSG = 'Upsert failed - ' + OBJECT_NAME(@@PROCID)  
   SET @ERRMSG = @ERRMSG + ' Error Code: ' + RTRIM(CONVERT(CHAR, @ERRORCODE))   
   RAISERROR (@ERRMSG, 16, 1)  
   RETURN (-1)  
END

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Inserts or updates a record in the Storage table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'UpsertStorage'
GO
